package com.test.account.dao;

import com.test.account.pojo.Account;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.*;
import java.util.Date;
import java.util.List;

/**
 * Created by 57695 on 2017/3/12.
 */
@Repository
public class AccountDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Transactional(readOnly = true)
    public List<Account> queryAll(){
        String sql = "select * from account";
        List<Account> accounts = jdbcTemplate.query(sql, new AccountRowMapper());
        return accounts;
    }
    @Transactional(readOnly = true)
    public Account queryByCode(long code){
        String sql = "select * from account where ac_code=?";
        Account account = jdbcTemplate.queryForObject(sql, new Object[]{code},new AccountRowMapper());
        return account;
    }
    public int  deleteByCode(long code){
        String sql = "delete from account where ac_code=?";
        int result = jdbcTemplate.update(sql,new Object[]{code});
        return result;
    }

    public int updateByCode(Account account,long code){
        String sql = "update account set ac_code=?,ac_name = ?,ac_remark=?,ac_state=?,ac_createtime=?,ac_logintime=?,ac_type=? where ac_code=?";
        int result = jdbcTemplate.update(sql,new Object[]{account.getAc_code(),account.getAc_name(),account.getAc_remark(),account.getAc_state(),account.getAc_createtime(),account.getAc_logintime(),account.getAc_type(),code});
        return result;
    }
    public int insert(Account account){
        String sql = "insert into account(ac_code,ac_name,ac_remark,ac_state,ac_type) values(?,?,?,?,?)";
        KeyHolder holder = new GeneratedKeyHolder();
        int x=1,y=2,z=3,u=4,v=5;
        int result = jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setLong(x,account.getAc_code());
                ps.setString(y,account.getAc_name());
                ps.setString(z,account.getAc_remark());
                ps.setInt(u,account.getAc_state());
                ps.setInt(v,account.getAc_type());
                return ps;
            }
        },holder);
        return result;
    }
    public List<Account> queryBySql(String sql){
        List<Account> accounts = jdbcTemplate.query(sql, new AccountRowMapper());
        return accounts;
    }
}
class AccountRowMapper implements RowMapper<Account>{

    @Override
    public Account mapRow(ResultSet resultSet, int i) throws SQLException {
        Account account = new Account();
        account.setAc_code(resultSet.getLong("ac_code"));
        account.setAc_name(resultSet.getString("ac_name"));
        account.setAc_remark(resultSet.getString("ac_remark"));
        account.setAc_state(resultSet.getInt("ac_state"));
        account.setAc_createtime(resultSet.getString("ac_createtime").substring(0,19));
        account.setAc_logintime(resultSet.getString("ac_logintime").substring(0,19));
        account.setAc_type(resultSet.getInt("ac_type"));
        return account;
    }
}